触发器类型 (BEFORE, AFTER, INSTEAD OF)

概述

触发器（Trigger）是数据库管理系统中的一种特殊类型的存储过程，它在特定的数据库操作（如插入、更新或删除）发生时自动执行。触发器可以用于维护数据完整性、审计日志、自动更新相关数据等。触发器有三种主要类型：BEFORE、AFTER 和 INSTEAD OF。每种类型都有其特定的应用场景和行为。

触发器类型

1. BEFORE 触发器

BEFORE 触发器在触发事件（如 INSERT、UPDATE 或 DELETE）发生之前执行。它可以用于在数据进入数据库之前对其进行验证或修改。

代码示例

CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    END IF;
END;

解释

• BEFORE INSERT ON employees: 在 employees 表上插入数据之前触发。
• FOR EACH ROW: 对每一行数据执行触发器。
• NEW.salary: 表示即将插入的数据中的 salary 字段。
• IF NEW.salary < 0 THEN SET NEW.salary = 0;: 如果薪水小于 0，则将其设置为 0。

2. AFTER 触发器

AFTER 触发器在触发事件发生之后执行。它通常用于在数据更新后执行某些操作，如记录日志或更新相关表。

代码示例

CREATE TRIGGER after_insert_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, audit_date)
    VALUES (NEW.id, 'INSERT', NOW());
END;

解释

• AFTER INSERT ON employees: 在 employees 表上插入数据之后触发。
• INSERT INTO employee_audit ...: 将插入操作记录到 employee_audit 表中。
• NEW.id: 表示新插入的员工 ID。
• NOW(): 获取当前时间。

3. INSTEAD OF 触发器

INSTEAD OF 触发器用于视图（View）上，它替代了触发事件的执行。通常用于处理复杂的视图操作，如在视图中插入数据时执行特定的逻辑。

代码示例

CREATE VIEW employee_view AS
SELECT id, name, salary FROM employees;

CREATE TRIGGER instead_of_insert_employee
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (name, salary)
    VALUES (NEW.name, NEW.salary);
END;

解释

• CREATE VIEW employee_view ...: 创建一个视图 employee_view。
• INSTEAD OF INSERT ON employee_view: 在视图 employee_view 上插入数据时触发。
• INSERT INTO employees ...: 将数据插入到实际的 employees 表中。

实践练习

练习 1: BEFORE 触发器

创建一个 BEFORE 触发器，确保插入到 products 表中的价格不能为负数。如果价格为负数，将其设置为 0。

CREATE TRIGGER before_insert_product
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END;

练习 2: AFTER 触发器

创建一个 AFTER 触发器，在 orders 表中插入新订单时，自动更新 customers 表中的 last_order_date 字段。

CREATE TRIGGER after_insert_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE customers
    SET last_order_date = NOW()
    WHERE id = NEW.customer_id;
END;

练习 3: INSTEAD OF 触发器

创建一个视图 customer_orders_view，显示每个客户的订单数量。然后创建一个 INSTEAD OF 触发器，允许通过视图插入新订单。

CREATE VIEW customer_orders_view AS
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

CREATE TRIGGER instead_of_insert_order
INSTEAD OF INSERT ON customer_orders_view
FOR EACH ROW
BEGIN
    INSERT INTO orders (customer_id)
    VALUES (NEW.customer_id);
END;

总结

触发器是数据库管理中的强大工具，能够自动化处理数据操作前后的逻辑。通过 BEFORE、AFTER 和 INSTEAD OF 触发器，可以实现数据验证、日志记录、复杂视图操作等功能。掌握这些触发器的使用，将有助于提高数据库操作的效率和数据完整性。